﻿#ifndef ___PCLIB_S_MYSQL___
#define ___PCLIB_S_MYSQL___


#include "../m_core/m_core.hpp"
#include "s_db.hpp"
#include "mysql-connector-c-6.1.11/include/mysql.h"


/**
*@brief libmysql对象类单例，用于加载mysql的client lib
*/
class CPCMySQLLib : CPCNoCopyable
{
public:
    CPCMySQLLib()
        : m_mysql_server_init(NULL)
        , m_mysql_server_end(NULL)
    {
    }
    virtual ~CPCMySQLLib()
    {
    }

    static CPCMySQLLib& obj()
    {
        static CPCMySQLLib o;
        return o;
    }

    /**
    *@brief     加载数据库lib
    *@param     dir         [in]    lib文件目录
    *@param     filename    [in]    lib文件名，如果传入空，windows使用libmysql.dll，linux使用libmysqlclient.so
    *@return    是否成功
    */
    CPCResult<> LoadLibMySQL(const char* dir, const char* filename = NULL)
    {
        CPCResult<> result;
        std::string path = dir ? dir : "";
        CPCFileUtil::PCDirFormat(path);
        if (filename == NULL || filename[0] == 0)
        {
#if defined(PCOS_WIN)
            path += "libmysql.dll";
#else
            path += "libmysqlclient.so";
#endif
        }
        else
        {
            path += filename;
        }
        auto ret = m_lib.LoadLib(path.c_str(), 
            "mysql_server_init|mysql_server_end|mysql_init|mysql_options|mysql_real_connect|mysql_store_result|mysql_free_result|"
            "mysql_next_result|mysql_query|mysql_fetch_row|mysql_errno|mysql_error|mysql_close|mysql_affected_rows|mysql_num_rows|"
            "mysql_num_fields|mysql_select_db|mysql_set_character_set|mysql_fetch_lengths|"
            "mysql_stmt_result_metadata|mysql_stmt_fetch_column|mysql_stmt_fetch|mysql_stmt_num_rows|mysql_stmt_store_result|"
            "mysql_stmt_next_result|mysql_stmt_bind_param|mysql_stmt_bind_result|mysql_stmt_execute|"
            "mysql_stmt_param_count|mysql_stmt_prepare|mysql_stmt_init|mysql_stmt_close");
        if (!ret)
        {
            return result.SetFail("%s", ret.ErrDesc());
        }
        if (!ret.Get().empty())
        {
            return result.SetFail("some func lost:%s", ret.Get().c_str());
        }
        m_mysql_server_init = (decltype(m_mysql_server_init))m_lib.m_addrs["mysql_server_init"];
        m_mysql_server_end = (decltype(m_mysql_server_end))m_lib.m_addrs["mysql_server_end"];
        m_mysql_init = (decltype(m_mysql_init))m_lib.m_addrs["mysql_init"];
        m_mysql_options = (decltype(m_mysql_options))m_lib.m_addrs["mysql_options"];
        m_mysql_real_connect = (decltype(m_mysql_real_connect))m_lib.m_addrs["mysql_real_connect"];
        m_mysql_store_result = (decltype(m_mysql_store_result))m_lib.m_addrs["mysql_store_result"];
        m_mysql_free_result = (decltype(m_mysql_free_result))m_lib.m_addrs["mysql_free_result"];
        m_mysql_next_result = (decltype(m_mysql_next_result))m_lib.m_addrs["mysql_next_result"];
        m_mysql_query = (decltype(m_mysql_query))m_lib.m_addrs["mysql_query"];
        m_mysql_fetch_row = (decltype(m_mysql_fetch_row))m_lib.m_addrs["mysql_fetch_row"];
        m_mysql_errno = (decltype(m_mysql_errno))m_lib.m_addrs["mysql_errno"];
        m_mysql_error = (decltype(m_mysql_error))m_lib.m_addrs["mysql_error"];
        m_mysql_close = (decltype(m_mysql_close))m_lib.m_addrs["mysql_close"];
        m_mysql_affected_rows = (decltype(m_mysql_affected_rows))m_lib.m_addrs["mysql_affected_rows"];
        m_mysql_num_rows = (decltype(m_mysql_num_rows))m_lib.m_addrs["mysql_num_rows"];
        m_mysql_num_fields = (decltype(m_mysql_num_fields))m_lib.m_addrs["mysql_num_fields"];
        m_mysql_select_db = (decltype(m_mysql_select_db))m_lib.m_addrs["mysql_select_db"];
        m_mysql_set_character_set = (decltype(m_mysql_set_character_set))m_lib.m_addrs["mysql_set_character_set"]; 
        m_mysql_fetch_lengths = (decltype(m_mysql_fetch_lengths))m_lib.m_addrs["mysql_fetch_lengths"];

        m_mysql_stmt_result_metadata = (decltype(m_mysql_stmt_result_metadata))m_lib.m_addrs["mysql_stmt_result_metadata"];
        m_mysql_stmt_fetch_column = (decltype(m_mysql_stmt_fetch_column))m_lib.m_addrs["mysql_stmt_fetch_column"];
        m_mysql_stmt_fetch = (decltype(m_mysql_stmt_fetch))m_lib.m_addrs["mysql_stmt_fetch"];
        m_mysql_stmt_num_rows = (decltype(m_mysql_stmt_num_rows))m_lib.m_addrs["mysql_stmt_num_rows"];
        m_mysql_stmt_store_result = (decltype(m_mysql_stmt_store_result))m_lib.m_addrs["mysql_stmt_store_result"];
        m_mysql_stmt_next_result = (decltype(m_mysql_stmt_next_result))m_lib.m_addrs["mysql_stmt_next_result"];
        m_mysql_stmt_bind_param = (decltype(m_mysql_stmt_bind_param))m_lib.m_addrs["mysql_stmt_bind_param"];
        m_mysql_stmt_bind_result = (decltype(m_mysql_stmt_bind_result))m_lib.m_addrs["mysql_stmt_bind_result"];
        m_mysql_stmt_execute = (decltype(m_mysql_stmt_execute))m_lib.m_addrs["mysql_stmt_execute"];
        m_mysql_stmt_param_count = (decltype(m_mysql_stmt_param_count))m_lib.m_addrs["mysql_stmt_param_count"];
        m_mysql_stmt_prepare = (decltype(m_mysql_stmt_prepare))m_lib.m_addrs["mysql_stmt_prepare"];
        m_mysql_stmt_init = (decltype(m_mysql_stmt_init))m_lib.m_addrs["mysql_stmt_init"];
        m_mysql_stmt_close = (decltype(m_mysql_stmt_close))m_lib.m_addrs["mysql_stmt_close"];

        if (m_mysql_server_init(0, NULL, NULL))
        {
            return result.SetFail("could not initialize MySQL client library");
        }
        return result;
    }

    /**
    *@brief     卸载数据库lib
    */
    void FreeLibMySQL()
    {
        if (m_mysql_server_end)
        {
            m_mysql_server_end();
            m_mysql_server_end = NULL;
        }
        m_lib.FreeLib();
    }

public:
    //c接口列表
    decltype(mysql_server_init)* m_mysql_server_init;
    decltype(mysql_server_end)* m_mysql_server_end;
    decltype(mysql_init)*   m_mysql_init;
    decltype(mysql_options)* m_mysql_options;
    decltype(mysql_real_connect)* m_mysql_real_connect;
    decltype(mysql_store_result)* m_mysql_store_result;
    decltype(mysql_free_result)* m_mysql_free_result;
    decltype(mysql_next_result)* m_mysql_next_result;
    decltype(mysql_query)* m_mysql_query;
    decltype(mysql_fetch_row)* m_mysql_fetch_row;
    decltype(mysql_errno)* m_mysql_errno;
    decltype(mysql_error)* m_mysql_error;
    decltype(mysql_close)* m_mysql_close;
    decltype(mysql_affected_rows)* m_mysql_affected_rows;
    decltype(mysql_num_rows)* m_mysql_num_rows;
    decltype(mysql_num_fields)* m_mysql_num_fields;
    decltype(mysql_select_db)* m_mysql_select_db;
    decltype(mysql_set_character_set)* m_mysql_set_character_set;
    decltype(mysql_fetch_lengths)* m_mysql_fetch_lengths;
    
    decltype(mysql_stmt_result_metadata)* m_mysql_stmt_result_metadata;
    decltype(mysql_stmt_fetch_column)* m_mysql_stmt_fetch_column;
    decltype(mysql_stmt_fetch)* m_mysql_stmt_fetch;
    decltype(mysql_stmt_num_rows)* m_mysql_stmt_num_rows;
    decltype(mysql_stmt_store_result)* m_mysql_stmt_store_result;
    decltype(mysql_stmt_next_result)* m_mysql_stmt_next_result;
    decltype(mysql_stmt_bind_param)* m_mysql_stmt_bind_param;
    decltype(mysql_stmt_bind_result)* m_mysql_stmt_bind_result;
    decltype(mysql_stmt_execute)* m_mysql_stmt_execute;
    decltype(mysql_stmt_param_count)* m_mysql_stmt_param_count;
    decltype(mysql_stmt_prepare)* m_mysql_stmt_prepare;
    decltype(mysql_stmt_init)* m_mysql_stmt_init;
    decltype(mysql_stmt_close)* m_mysql_stmt_close;
private:
    CPCDllHandle m_lib;
};

/**
*@brief 数据库对象类
*       对于MYSQL8.0有可能连不上的情况，需要修改：
*   -- 修改密码为用不过期
    ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; 
    -- 修改密码并指定加密规则为mysql_native_password
    ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
    -- 刷新权限
    flush privileges;
*/
class CPCMySQLDataBase : CPCIDB
{
public:
    CPCMySQLDataBase()
        : m_Connected(false)
        , m_Lib(CPCMySQLLib::obj())
        , m_pMySQL(NULL)
        , m_ResPtr(NULL)
        , m_SqlRow(NULL)
        , m_dwTableRow(0)
        , m_dwTableCol(0)
    {
    }
    virtual ~CPCMySQLDataBase()
    {
        this->Close();
    }

    //初始化数据库，可以指定依赖的lib位置
    static CPCResult<> InitDB(const char* dir = NULL, const char* filename = NULL)
    {
        return CPCMySQLLib::obj().LoadLibMySQL(dir, filename);
    }

    //停止数据库
    static void CleanupDB()
    {
        CPCMySQLLib::obj().FreeLibMySQL();
    }

    /**
    *@brief     打开数据库
    *@param     pszHost[in]     主机名
    *@param     usPort[in]      端口
    *@param     pszUser[in]     用户名
    *@param     pszPassword[in] 密码
    *@param     pszDB[in]       数据库名，可以为空，代表不指定数据库，后续通过SelectDB函数进行选择
    *@param     pszChars[in]    数据库字符集，为空代表不设置
    *@param     nCTimeOut[in]   连接数据库的超时时间，等于0时不设置
    *@param     nRWTimeOut[in]  读写数据库的超时时间，等于0时不设置
    *@return    是否成功
    */
    CPCResult<> Open(const char* pszHost, unsigned short usPort, const char* pszUser, const char* pszPassword, 
        const char* pszDB = NULL, const char* pszChars = NULL,
        unsigned int nCTimeOut = 30000, unsigned int nRWTimeOut = 30000) override
    {
        CPCResult<> result;
        if (pszHost == NULL || pszUser == NULL || pszPassword == NULL)
        {
            return result.SetFail("pszHost == NULL || pszUser == NULL || pszPassword == NULL");
        }
        if (m_Lib.m_mysql_init == NULL)
        {
            return result.SetFail("Please call this function first : CPCMySQLLib::obj().LoadLibMySQL();");
        }

        //初始化连接句柄  
        this->Close();
        m_pMySQL = m_Lib.m_mysql_init(NULL);
        if (m_pMySQL == NULL)
        {
            return result.SetFail("mysql_init() == NULL");
        }

        //设置连接超时
        result = SetTimeOutProps(nCTimeOut, MYSQL_OPT_CONNECT_TIMEOUT);
        if (!result)
        {
            return result;
        }

        //设置读超时
        result = SetTimeOutProps(nRWTimeOut / 3, MYSQL_OPT_READ_TIMEOUT);
        if (!result)
        {
            return result;
        }

        //设置写超时
        result = SetTimeOutProps(nRWTimeOut / 2, MYSQL_OPT_WRITE_TIMEOUT);
        if (!result)
        {
            return result;
        }

        //连接
        if (m_Lib.m_mysql_real_connect(m_pMySQL, pszHost, pszUser, pszPassword, pszDB, usPort, NULL, CLIENT_MULTI_STATEMENTS) == NULL)
        {
            return result.SetFail(LstErrorNo(), "mysql_real_connect FAIL: %s", LstError());
        }

        //设置字符集
        if (pszChars != NULL && pszChars[0] != 0)
        {
            if (0 != m_Lib.m_mysql_set_character_set(m_pMySQL, pszChars))
            {
                return result.SetFail(LstErrorNo(), "mysql_set_character_set(%s) FAIL: %s", pszChars, LstError());
            }
        }
        
        //设置为自动重连
        char value = 1;
        m_Lib.m_mysql_options(m_pMySQL, MYSQL_OPT_RECONNECT, &value);
        return this->TestConnect();
    }

    /**
    *@brief     关闭数据库
    */
    void Close() override
    {
        m_Connected = false;
        if (m_Lib.m_mysql_init == NULL)
        {
            return;
        }
        CleanResultSet();
        if (m_pMySQL)
        {
            m_Lib.m_mysql_close(m_pMySQL);
            m_pMySQL = NULL;
        }
    }

    /**
    *@brief        数据库是否打开
    */
    bool IsOpen() const override
    {
        return (m_Connected && m_Lib.m_mysql_init != NULL && m_pMySQL != NULL);
    }

    /**
    *@brief     选择数据库
    *@param     pszDB[in]       数据库名
    *@return    是否成功
    */
    CPCResult<> SelectDB(const char* pszDB)
    {
        CPCResult<> result;
        if (pszDB == NULL || pszDB[0] == 0)
        {
            return result.SetFail("pszDB == NULL || pszDB[0] == 0");
        }
        if (0 != m_Lib.m_mysql_select_db(m_pMySQL, pszDB))
        {
            return result.SetFail(LstErrorNo(), "mysql_select_db(%s) FAIL: %s", pszDB, LstError());
        }
        return result;
    }

    /**
    *@brief     测试连接
    */
    CPCResult<> TestConnect(void)
    {
        CPCResult<> result;
        m_Connected = false;
        auto retsql = this->ExecuteSQL("select 1");
        if (!retsql)
        {
            return result.SetFail("%s", retsql.ErrDesc());
        }
        if (m_dwTableRow != 1)
        {
            return result.SetFail("m_dwTableRow != 1");
        }
        m_Connected = true;
        return result;
    }

    /**
    *@brief     清除结果集
    */
    void CleanResultSet(void)
    {
        m_dwTableRow = 0;
        m_dwTableCol = 0;
        if (m_ResPtr != NULL)
        {
            m_Lib.m_mysql_free_result(m_ResPtr);
            m_ResPtr = NULL;
            m_SqlRow = NULL;
        }
    }

    /**
    *@brief     执行sql语句
    *@param     sql      [in]    sql语句
    *@return    是否成功。uint64_t意义(SELECT：结果行数，其他：修改的行数量)
    */
    CPCResult<uint64_t> ExecuteSQL(const char* pszSQL)
    {
        CPCResult<uint64_t> result;
        if (NULL == pszSQL || pszSQL[0] == 0)
        {
            return result.SetFail("NULL == pszSQL || pszSQL[0] == 0");
        }
        
        //清理历史数据，并读掉上一次的结果
        CleanResultSet();
        MYSQL_RES* pRes;
        do
        {
            pRes = m_Lib.m_mysql_store_result(m_pMySQL);
            m_Lib.m_mysql_free_result(pRes);
        } while (!m_Lib.m_mysql_next_result(m_pMySQL));

        if (m_Lib.m_mysql_query(m_pMySQL, pszSQL) != 0)
        {
            return result.SetFail(LstErrorNo(), "mysql_query(%s) FAIL: %s", pszSQL, LstError());
        }
        uint64_t num_affected = m_Lib.m_mysql_affected_rows(m_pMySQL);
        if (num_affected == -1)
        {
            num_affected = 0;
        }
        
        m_ResPtr = m_Lib.m_mysql_store_result(m_pMySQL);
    ExecuteUpdate__Next:
        if (m_ResPtr)
        {
            m_dwTableRow = m_Lib.m_mysql_num_rows(m_ResPtr);
            m_dwTableCol = m_Lib.m_mysql_num_fields(m_ResPtr);
            num_affected = m_dwTableRow;
        }
        MYSQL_RES* pTemp = m_Lib.m_mysql_store_result(m_pMySQL);
        if (pTemp != NULL)
        {
            CleanResultSet();
            m_ResPtr = pTemp;
            goto ExecuteUpdate__Next;
        }
        return result.SetSucc(num_affected);
    }

    /**
    *@brief     取结果集行数
    */
    uint64_t GetRow(void)
    {
        return m_dwTableRow;
    }

    /**
    *@brief     取结果集列数
    */
    unsigned int GetCol(void)
    {
        return m_dwTableCol;
    }

    /**
    *@brief    读取一行数据，可能返回三种结果：
    *          1.出错，CPCResult == false
    *          2.成功，且还有下一行，bool = true
    *          3.成功，没有下一行了，bool = false
    */
    CPCResult<bool> FetchRow(void)
    {
        CPCResult<bool> result;
        if (m_ResPtr == NULL)
        {
            return result.SetFail( "Please Execute Select SQL First.");
        }
        m_SqlRow = m_Lib.m_mysql_fetch_row(m_ResPtr);
        if (m_SqlRow)
        {
            return result.SetSucc(true);
        }
        else
        {
            //读到最后一行，已无记录数据。
            int iError = m_Lib.m_mysql_errno(m_pMySQL);
            if (0 != iError)
            {
                return result.SetFail(iError, "mysql_fetch_row FAIL: %s", LstError());
            }
            else
            {
                return result.SetSucc(false);
            }
        }
    }

    /**
    *@brief     取字段名，失败返回空
    */
    const char* GetColName(unsigned int dwCol)
    {
        if (m_ResPtr == NULL )
        {
            return "";
        }
        if (dwCol >= m_dwTableCol)
        {
            return "";
        }
        return m_ResPtr->fields[dwCol].name;
    }

    /**
    *@brief     取字段数据，失败返回空
    */
    const char* GetColData(unsigned int dwCol)
    {
        if (m_ResPtr == NULL || m_SqlRow == NULL )
        {
            return NULL;
        }
        if (dwCol >= m_dwTableCol)
        {
            return NULL;
        }
        return m_SqlRow[dwCol];
    }

    /**
    *@brief     根据字段名搜索字段数据，失败返回空
    */
    const char* SearchColData(const char* pszColName)
    {
        if (m_ResPtr == NULL || m_SqlRow == NULL )
        {
            return NULL;
        }
        if (pszColName == NULL || pszColName[0] == 0)
        {
            return NULL;
        }
       
        //搜索
        for (unsigned int i = 0; i < m_dwTableCol; i++)
        {
            if (strcmp(pszColName, m_ResPtr->fields[i].name) == 0)
            {
                return m_SqlRow[i];
            }
        }

        return NULL;
    }

    /**
    *@brief     打印结果集，仅用于调试
    */
    void PrintResult(void)
    {
        printf("\t");
        for (unsigned int j = 0; j < GetCol(); j++)
        {
            const char* name = GetColName(j);
            printf("%s\t", name);
        }
        printf("\n=========");
        for (unsigned int j = 0; j < GetCol(); j++)
        {
            printf("=========");
        }
        printf("\n");

        for (uint64_t i = 0; i < GetRow(); i++)
        {
            auto r2 = FetchRow();
            if (!r2)
            {
                printf("err:%s\n", r2.ErrDesc());
                break;
            }
            if (!r2.Get())
            {
                printf("no more data, break...\n");
                break;
            }

            printf("[%llu]\t", i);
            for (unsigned int j = 0; j < GetCol(); j++)
            {
                const char* value = GetColData(j);
                printf("%s\t", value);
            }
            printf("\n");
        }
    }

private:
    /**
    *@brief     最后的错误码
    */
    int LstErrorNo(void)
    {
        if (m_pMySQL == NULL)
        {
            return -1;
        }
        return m_Lib.m_mysql_errno(m_pMySQL);
    }

    /**
    *@brief     最后的错误描述
    */
    const char* LstError(void)
    {
        if (m_pMySQL == NULL)
        {
            return "m_pMySQL == NULL";
        }
        return m_Lib.m_mysql_error(m_pMySQL);
    }

    /**
    *@brief     设置超时时间（毫秒)
    */
    CPCResult<> SetTimeOutProps(unsigned int nTimeOut, mysql_option option)
    {
        CPCResult<> result;
        if (m_pMySQL == NULL)
        {
            return result.SetFail("m_pMySQL == NULL");
        }
        if (nTimeOut > 0)
        {
            nTimeOut = nTimeOut / 1000;
            int iRet = m_Lib.m_mysql_options(m_pMySQL, option, (const char*)&nTimeOut);
            if (iRet != 0)
            {
                return result.SetFail(LstErrorNo(), "option(%d) FAIL: %s", (int)option, LstError());
            }
        }
        return result;
    }

private:
    bool            m_Connected;
    CPCMySQLLib&    m_Lib;
    MYSQL*          m_pMySQL;
    MYSQL_RES*      m_ResPtr;
    MYSQL_ROW       m_SqlRow;
    uint64_t        m_dwTableRow;
    unsigned int    m_dwTableCol;
};



#endif    //___PCLIB_S_MYSQL___